﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using System.Collections;
using System.Data.Objects;
using System.Data.Objects.SqlClient;
using System.Data.Common;
using XBoshiGFS.forms;


namespace XBoshiGFS.reports
{
    public partial class WaterUsageInforQueryForm : Form
    {
        XBoshiGFSModelContainer context = new XBoshiGFSModelContainer();
        IQueryable  orgSql;
        String strGroupID;





        public WaterUsageInforQueryForm()
        {
            InitializeComponent();



            IList<StationSet> list = (from p in context.StationSet  select p).ToList();
            list.Insert(0, new StationSet() { Id = "", strName = "", strCode = "" });
            cmbStation.DataSource = list;

            //灌季
            cmbWaterSeason.DataSource = (from p in context.WaterSeasonSet orderby p.strName   select p);

        }
 
 

        private void cmbStation_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;

            IList<SegmentCourseSet> list = (from p in context.SegmentCourseSet where p.StationId == strID orderby p.strName select p).ToList();
            list.Insert(0, new SegmentCourseSet() {Id ="",strName ="",strCode ="" });
            cmbSegment.DataSource = list;
        }

        private void cmbSegment_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            IList<WaterCourseSet> list = (from p in context.WaterCourseSet where p.SegmentCourseId == strID orderby p.strName select p).ToList();
            list.Insert(0, new WaterCourseSet() { Id = "", strName = "", strCode = "" });

            cmbWaterCourse.DataSource = list ;

        }

        private void cmbWaterCourse_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
        //    cmbVillage.DataSource = (from p in context.VillageSet where p.WaterCourseId == strID orderby p.strName select p);
            IList<VillageSet> list =(from p in context.VillageSet where p.WaterCourseId == strID orderby p.strName select p).ToList();
            list.Insert(0, new VillageSet() { Id = "", strName = "", strCode = "" });
            cmbVillage.DataSource = list;
        }

        private void cmbVillage_SelectedIndexChanged(object sender, EventArgs e)
        {
            var strID = ((ComboBox)sender).SelectedValue;
            IList<GroupSet> list = (from p in context.GroupSet where p.VillageId == strID orderby p.strName select p).ToList();
            list.Insert(0, new GroupSet() { Id = "", strName = "", strCode = "" });
            cmbGroup.DataSource = list;
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            Cursor = Cursors.WaitCursor;
            try
            {
                strGroupID = (String)cmbGroup.SelectedValue;
                var sql = GetQuerySql();
                waterUsageBillsReportDataBindingSource.DataSource = sql;


            }
            catch (Exception ex)
            {

            }
            Cursor = Cursors.Default;
        }
        //ProcessBarForm pr = null;
        //String strSql = "";
        //private void btnQuery_Click(object sender, EventArgs e)
        //{
        //    strSql = getSQLStr();
        //    pr = new ProcessBarForm();
        //    //   pr.Parent  = this;



        //    Action handler = new Action(queryData);
        //    handler.BeginInvoke(
        //        new AsyncCallback(this.AsyncCallback),
        //        handler
        //        );
        //    pr.StartTimer(50);
        //    pr.ShowDialog();

        //}
        //private String getSQLStr()
        //{
        //    String strPeopleName = TBName.Text;
        //    String strPeopleCode = TBCode.Text;
        //    String strStationID = (String)cmbStation.SelectedValue;
        //    String strSegementID = (String)cmbSegment.SelectedValue;
        //    String strWaterCourseID = (String)cmbWaterCourse.SelectedValue;
        //    String strVillageID = (String)cmbVillage.SelectedValue;
        //    String strGroupID = (String)cmbGroup.SelectedValue;

        //    String strSQl =
        //    " SELECT  " +
        //    " 0.0 AS decPrice, " +
        //    " t.UseArea AS decUseArea, " +
        //    " p.Id AS strGropId, " +
        //    " o.strCode AS strGroupCode, " +
        //    " o.strManager AS strGroupManager, " +
        //    " o.strName AS strGroupName, " +
        //    " t.strCode AS strPeopleCode, " +
        //    " t.Id AS strPeopleID, " +
        //    " t.strName AS strPeopleName, " +
        //    " r.strCode AS strSegmentCode, " +
        //    " r.strName AS strSegmentName, " +
        //    " s.strCode AS strStationCode, " +
        //    " s.strName AS strStationName, " +
        //    " p.strCode AS strVillageCode, " +
        //    " p.strName AS strVillageName, " +
        //    " q.strCode AS strWaterCourseCode, " +
        //    " q.strName AS strWaterCourseName, " +
        //    " q.strName AS strWaterSeasonName "
        //    + "  FROM      PeopleSet AS t INNER JOIN "
        //    + "   GroupSet AS o ON t.GroupId = o.Id INNER JOIN "
        //    + "  VillageSet AS p ON o.VillageId = p.Id INNER JOIN "
        //    + "    WaterCourseSet AS q ON p.WaterCourseId = q.Id INNER JOIN "
        //    + "    SegmentCourseSet AS r ON q.SegmentCourseId = r.Id INNER JOIN "
        //    + "    StationSet AS s ON r.StationId = s.Id "
        //    + "  where 1=1 ";
        //    if (strPeopleName != "")
        //    {
        //        strSQl = strSQl + " AND t.strName LIKE ('%" + strPeopleName + "%') ";
        //    }
        //    if (strPeopleCode != "")
        //    {
        //        strSQl = strSQl + " AND t.strCode LIKE ('%" + strPeopleCode + "%') ";
        //    }
        //    if (strStationID != "")
        //    {
        //        strSQl = strSQl + " AND s.Id ='" + strStationID + "' ";
        //    }
        //    if (strSegementID != "")
        //    {
        //        strSQl = strSQl + " AND r.Id ='" + strSegementID + "' ";
        //    }
        //    if (strWaterCourseID != "")
        //    {
        //        strSQl = strSQl + " AND q.Id ='" + strWaterCourseID + "' ";
        //    }
        //    if (strVillageID != "")
        //    {
        //        strSQl = strSQl + " AND p.Id ='" + strVillageID + "' ";
        //    }
        //    if (strGroupID != "")
        //    {
        //        strSQl = strSQl + " AND o.Id ='" + strGroupID + "' ";
        //    }
        //    return strSQl;



        //}
        //private void queryData()
        //{
        //    try
        //    {


        //        var sql = GetQuerySql(strSql);
        //        //  waterBillReportDataBindingSource.DataSource = sql;

        //        System.Windows.Forms.MethodInvoker invoker = () => waterBillReportDataBindingSource.DataSource = sql;
        //        if (this.InvokeRequired)
        //        {
        //            this.Invoke(invoker);
        //        }
        //        else
        //        {
        //            invoker();
        //        }


        //    }
        //    catch (Exception ex)
        //    {

        //    }

        //}
        //// 结束异步操作
        //private void AsyncCallback(IAsyncResult ar)
        //{
        //    // 标准的处理步骤
        //    Action handler = ar.AsyncState as Action;
        //    handler.EndInvoke(ar);
        //    System.Windows.Forms.MethodInvoker invoker = () => pr.Dispose();
        //    if (this.InvokeRequired)
        //    {
        //        this.Invoke(invoker);
        //    }
        //    else
        //    {
        //        invoker();
        //    }


        //}
        private IList<WaterUsageBillsReportData> GetQuerySql()
        {

            String strPeopleName = TBName.Text;
            String strPeopleCode = TBCode.Text;
            String strStationID = (String)cmbStation.SelectedValue;
            String strSegementID = (String)cmbSegment.SelectedValue;
            String strWaterCourseID = (String)cmbWaterCourse.SelectedValue;
            String strVillageID = (String)cmbVillage.SelectedValue;
            String strGroupID = (String )cmbGroup.SelectedValue;
            String strWaterSeasonId = (String)cmbWaterSeason.SelectedValue; 
            String strSQl = "";

            if (!String.IsNullOrWhiteSpace(strGroupID))
            {
                //指定了组
                strSQl = " SELECT  "
                  + " o.strCode AS strGroupCode, "
                  + " o.strName AS strGroupName, "
                  + " p.strCode AS strVillageCode, "
                  + " p.strName AS strVillageName, "
                  + " q.strCode AS strWaterCourseCode, "
                  + " q.strName AS strWaterCourseName, "
                  + " r.strCode AS strSegmentCode, "
                  + " r.strName AS strSegmentName, "
                  + " s.strCode AS strStationCode, "
                  + " s.strName AS strStationName, "
                  + " sum(u.UseArea)   AS decUseArea ,"
                  + " sum(u.UseLong)   AS intUseLong ,"
                  + " sum(u.WaterBill) AS decWaterBill ,"
                  + " sum(u.WaterCap)  AS decWaterCap "
                  + "  FROM  PeopleSet AS t "
                  + "  INNER JOIN  GroupSet AS o ON t.GroupId = o.Id "
                  + "  INNER JOIN  VillageSet AS p ON o.VillageId = p.Id "
                  + "  INNER JOIN  WaterCourseSet AS q ON p.WaterCourseId = q.Id "
                  + "  INNER JOIN  SegmentCourseSet AS r ON q.SegmentCourseId = r.Id"
                  + "  INNER JOIN  StationSet AS s ON r.StationId = s.Id"
                  + "   INNER JOIN  WaterUsageSet as  u On u.PeopleId = t.Id "
                  + "   INNER JOIN  WaterSeasonSet as v On v.id=u.WaterSeasonId"
                  + " WHERE 1=1 "
                  + " AND s.Id ='" + strStationID + "' "
                  + " AND r.Id ='" + strSegementID + "' "
                  + " AND q.Id ='" + strWaterCourseID + "' "
                  + " AND p.Id ='" + strVillageID + "' "
                                  + " AND v.Id ='" + strWaterSeasonId + "' "
                  + " AND o.Id ='" + strGroupID + "' "
                  + " GROUP BY o.strCode,o.strName,p.strCode,p.strName,"
                  + "           q.strCode,q.strName,r.strCode,r.strName, "
                  + "           s.strCode,s.strName "
                  ;
            }
            else
            {
                //没有指定组
                if (!String.IsNullOrWhiteSpace(strVillageID))
                {
                    //指定了村
                    strSQl = " SELECT  "
                      + " o.strCode AS strGroupCode, "
                      + " o.strName AS strGroupName, "
                      + " p.strCode AS strVillageCode, "
                      + " p.strName AS strVillageName, "
                      + " q.strCode AS strWaterCourseCode, "
                      + " q.strName AS strWaterCourseName, "
                      + " r.strCode AS strSegmentCode, "
                      + " r.strName AS strSegmentName, "
                      + " s.strCode AS strStationCode, "
                      + " s.strName AS strStationName, "
                      + " sum(u.UseArea)   AS decUseArea ,"
                      + " sum(u.UseLong)   AS intUseLong ,"
                      + " sum(u.WaterBill) AS decWaterBill ,"
                      + " sum(u.WaterCap)  AS decWaterCap "
                      + "  FROM  PeopleSet AS t "
                      + "  INNER JOIN  GroupSet AS o ON t.GroupId = o.Id "
                      + "  INNER JOIN  VillageSet AS p ON o.VillageId = p.Id "
                      + "  INNER JOIN  WaterCourseSet AS q ON p.WaterCourseId = q.Id "
                      + "  INNER JOIN  SegmentCourseSet AS r ON q.SegmentCourseId = r.Id"
                      + "  INNER JOIN  StationSet AS s ON r.StationId = s.Id"
                      + "   INNER JOIN  WaterUsageSet as  u On u.PeopleId = t.Id "
                      + "   INNER JOIN  WaterSeasonSet as v On v.id=u.WaterSeasonId"
                      + " WHERE 1=1 "
                      + " AND s.Id ='" + strStationID + "' "
                      + " AND r.Id ='" + strSegementID + "' "
                      + " AND q.Id ='" + strWaterCourseID + "' "
                      + " AND p.Id ='" + strVillageID + "' "
                                  + " AND v.Id ='" + strWaterSeasonId + "' "
                   + " GROUP BY o.strCode,o.strName,p.strCode,p.strName,"
                  + "           q.strCode,q.strName,r.strCode,r.strName, "
                  + "           s.strCode,s.strName "
                      ;
                }
                else
                {
                    //没有指定村
                    if (!String.IsNullOrWhiteSpace(strWaterCourseID))
                    {
                        //指定了斗
                        strSQl = " SELECT  "
                                  + " '' AS strGroupCode, "
                                  + " '' AS strGroupName, "
                          + " p.strCode AS strVillageCode, "
                          + " p.strName AS strVillageName, "
                          + " q.strCode AS strWaterCourseCode, "
                          + " q.strName AS strWaterCourseName, "
                          + " r.strCode AS strSegmentCode, "
                          + " r.strName AS strSegmentName, "
                          + " s.strCode AS strStationCode, "
                          + " s.strName AS strStationName, "
                          + " sum(u.UseArea)   AS decUseArea ,"
                          + " sum(u.UseLong)   AS intUseLong ,"
                          + " sum(u.WaterBill) AS decWaterBill ,"
                          + " sum(u.WaterCap)  AS decWaterCap "
                          + "  FROM  PeopleSet AS t "
                          + "  INNER JOIN  GroupSet AS o ON t.GroupId = o.Id "
                          + "  INNER JOIN  VillageSet AS p ON o.VillageId = p.Id "
                          + "  INNER JOIN  WaterCourseSet AS q ON p.WaterCourseId = q.Id "
                          + "  INNER JOIN  SegmentCourseSet AS r ON q.SegmentCourseId = r.Id"
                          + "  INNER JOIN  StationSet AS s ON r.StationId = s.Id"
                          + "   INNER JOIN  WaterUsageSet as  u On u.PeopleId = t.Id "
                          + "   INNER JOIN  WaterSeasonSet as v On v.id=u.WaterSeasonId"
                          + " WHERE 1=1 "
                          + " AND s.Id ='" + strStationID + "' "
                          + " AND r.Id ='" + strSegementID + "' "
                          + " AND q.Id ='" + strWaterCourseID + "' "
                                  + " AND v.Id ='" + strWaterSeasonId + "' "
                       + " GROUP BY p.strCode,p.strName,"
                      + "           q.strCode,q.strName,r.strCode,r.strName, "
                      + "           s.strCode,s.strName "
                          ;
                    }
                    else
                    {
                        //没有指定斗
                        if (!String.IsNullOrWhiteSpace(strSegementID))
                        {
                            //指定了支
                            strSQl = " SELECT  "
                                  + " '' AS strGroupCode, "
                                  + " '' AS strGroupName, "
                                  + " '' AS strVillageCode, "
                                  + " '' AS strVillageName, "
                              + "  q.strCode AS strWaterCourseCode, "
                              + "  q.strName AS strWaterCourseName, "
                              + " r.strCode AS strSegmentCode, "
                              + " r.strName AS strSegmentName, "
                              + " s.strCode AS strStationCode, "
                              + " s.strName AS strStationName, "
                              + " sum(u.UseArea)   AS decUseArea ,"
                              + " sum(u.UseLong)   AS intUseLong ,"
                              + " sum(u.WaterBill) AS decWaterBill ,"
                              + " sum(u.WaterCap)  AS decWaterCap "
                              + "  FROM  PeopleSet AS t "
                              + "  INNER JOIN  GroupSet AS o ON t.GroupId = o.Id "
                              + "  INNER JOIN  VillageSet AS p ON o.VillageId = p.Id "
                              + "  INNER JOIN  WaterCourseSet AS q ON p.WaterCourseId = q.Id "
                              + "  INNER JOIN  SegmentCourseSet AS r ON q.SegmentCourseId = r.Id"
                              + "  INNER JOIN  StationSet AS s ON r.StationId = s.Id"
                              + "   INNER JOIN  WaterUsageSet as  u On u.PeopleId = t.Id "
                              + "   INNER JOIN  WaterSeasonSet as v On v.id=u.WaterSeasonId"
                              + " WHERE 1=1 "
                              + " AND s.Id ='" + strStationID + "' "
                              + " AND r.Id ='" + strSegementID + "' "
                                  + " AND v.Id ='" + strWaterSeasonId + "' "
                              + " GROUP BY "
                          + "           q.strCode,q.strName,r.strCode,r.strName, "
                          + "           s.strCode,s.strName "
                              ;
                        }
                        else
                        {
                            //没有指定支
                            if (!String.IsNullOrWhiteSpace(strStationID))
                            {
                                //指定了站
                                strSQl = " SELECT  "
                                  + " '' AS strGroupCode, "
                                  + " '' AS strGroupName, "
                                  + " '' AS strVillageCode, "
                                  + " '' AS strVillageName, "
                                  + " '' AS strWaterCourseCode, "
                                  + " '' AS strWaterCourseName, "
                                  + " r.strCode AS strSegmentCode, "
                                  + " r.strName AS strSegmentName, "
                                  + " s.strCode AS strStationCode, "
                                  + " s.strName AS strStationName, "
                                  + " sum(u.UseArea)   AS decUseArea ,"
                                  + " sum(u.UseLong)   AS intUseLong ,"
                                  + " sum(u.WaterBill) AS decWaterBill ,"
                                  + " sum(u.WaterCap)  AS decWaterCap "
                                  + "  FROM  PeopleSet AS t "
                                  + "  INNER JOIN  GroupSet AS o ON t.GroupId = o.Id "
                                  + "  INNER JOIN  VillageSet AS p ON o.VillageId = p.Id "
                                  + "  INNER JOIN  WaterCourseSet AS q ON p.WaterCourseId = q.Id "
                                  + "  INNER JOIN  SegmentCourseSet AS r ON q.SegmentCourseId = r.Id"
                                  + "  INNER JOIN  StationSet AS s ON r.StationId = s.Id"
                                  + "   INNER JOIN  WaterUsageSet as  u On u.PeopleId = t.Id "
                                  + "   INNER JOIN  WaterSeasonSet as v On v.id=u.WaterSeasonId"
                                  + " WHERE 1=1 "
                                  + " AND s.Id ='" + strStationID + "' "
                                      + " AND v.Id ='" + strWaterSeasonId + "' "
                              + " GROUP BY "
                              + "            r.strCode,r.strName, "
                              + "           s.strCode,s.strName "
                                  ;
                            }
                            else
                            {
                                //没有指定站
                                strSQl = " SELECT  "
                                  + " '' AS strGroupCode, "
                                  + " '' AS strGroupName, "
                                  + " '' AS strVillageCode, "
                                  + " '' AS strVillageName, "
                                  + " '' AS strWaterCourseCode, "
                                  + " '' AS strWaterCourseName, "
                                  + " '' AS strSegmentCode, "
                                  + " '' AS strSegmentName, "
                                  + " s.strCode AS strStationCode, "
                                  + " s.strName AS strStationName, "
                                  + " sum(u.UseArea)   AS decUseArea ,"
                                  + " sum(u.UseLong)   AS intUseLong ,"
                                  + " sum(u.WaterBill) AS decWaterBill ,"
                                  + " sum(u.WaterCap)  AS decWaterCap "
                                  + "  FROM  PeopleSet AS t "
                                  + "  INNER JOIN  GroupSet AS o ON t.GroupId = o.Id "
                                  + "  INNER JOIN  VillageSet AS p ON o.VillageId = p.Id "
                                  + "  INNER JOIN  WaterCourseSet AS q ON p.WaterCourseId = q.Id "
                                  + "  INNER JOIN  SegmentCourseSet AS r ON q.SegmentCourseId = r.Id"
                                  + "  INNER JOIN  StationSet AS s ON r.StationId = s.Id"
                                  + "   INNER JOIN  WaterUsageSet as  u On u.PeopleId = t.Id "
                                  + "   INNER JOIN  WaterSeasonSet as v On v.id=u.WaterSeasonId"
                                  + " WHERE 1=1 "
                                  + " AND v.Id ='" + strWaterSeasonId + "' "
                                  + " GROUP BY "
                                  + "            "
                                  + "           s.strCode,s.strName "

                                  ;
                            }

                        }
                    }
                }
            }
            var query = context.CreateQuery<DbDataRecord>(strSQl);
            IList<WaterUsageBillsReportData> list = new List<WaterUsageBillsReportData>();
            foreach (var rs in query )
        {
            try
           {
               WaterUsageBillsReportData wb = new WaterUsageBillsReportData();
                wb.intUseLong = rs["intUseLong"] is DBNull ? 0.0M : decimal.Parse(rs["intUseLong"].ToString());
                wb.decUseArea = rs["decUseArea"] is DBNull ? 0.0M : decimal.Parse(rs["decUseArea"].ToString());
                wb.decWaterBill = rs["decWaterBill"] is DBNull ? 0.0M : decimal.Parse(rs["decWaterBill"].ToString()); ;
                wb.decWaterCap = rs["decWaterCap"] is DBNull ? 0.0M : decimal.Parse(rs["decWaterCap"].ToString()); ;

                wb.strGroupCode = rs["strGroupCode"] is DBNull ? "" : (String)rs["strGroupCode"];
                wb.strGroupName = rs["strGroupName"] is DBNull ? "" : (String)rs["strGroupName"];
                wb.strSegmentCode = rs["strSegmentCode"] is DBNull ? "" : (String)rs["strSegmentCode"];
                wb.strSegmentName = rs["strSegmentName"] is DBNull ? "" : (String)rs["strSegmentName"];
                wb.strStationCode = rs["strStationCode"] is DBNull ? "" : (String)rs["strStationCode"];
                wb.strStationName = rs["strStationName"] is DBNull ? "" : (String)rs["strStationName"];
                wb.strVillageCode = rs["strVillageCode"] is DBNull ? "" : (String)rs["strVillageCode"];
                wb.strVillageName = rs["strVillageName"] is DBNull ? "" : (String)rs["strVillageName"];
                wb.strWaterCourseCode = rs["strWaterCourseCode"] is DBNull ? "" : (String)rs["strWaterCourseCode"];
                wb.strWaterCourseName = rs["strWaterCourseName"] is DBNull ? "" : (String)rs["strWaterCourseName"]; 
                list.Add(wb); 
            }
            catch { 
            
            }

        }
            return list; 
        } 


        private void cmbGroup_SelectedIndexChanged(object sender, EventArgs e)
        {
    
        }

        private void cmbWaterSeason_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.btnQuery.Enabled = true;
        }

        private void WaterBillReportForm_Load(object sender, EventArgs e)
        {

        }

        private void tableLayoutPanel1_Paint(object sender, PaintEventArgs e)
        {

        }




        private void btnReset_Click(object sender, EventArgs e)
        {
            cmbStation.SelectedValue = "";
            TBCode.Text = "";
            TBName.Text = "";
        }


    }
}
